package c0907m.group2.aitsm.core.db;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Attendance table.
 */
public class AttendanceDb extends BaseDb {
    private String insert = "INSERT INTO attendance VALUES (?, ?, ?)";
    private String delete = "DELETE FROM attendance WHERE schedule_id = ?";

    /**
     * Get a map of Student ID - Present for the given Schedule.
     *
     * @param schedule_id the row's schedule_id to search.
     * @return list of subject_id found.
     * @throws SQLException if a database access error occurs.
     */
    public Map<Integer, Boolean> select(int schedule_id) throws SQLException {
        String select = "SELECT student_id, present FROM attendance WHERE schedule_id = " + schedule_id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            Map<Integer, Boolean> map = new HashMap<Integer, Boolean>();

            while (rs.next()) {
                map.put(rs.getInt(1), rs.getBoolean(2));
            }

            return map;
        } finally {
            close();
        }
    }

    /**
     * Update Attendances for the given Schedule.
     *
     * @param schedule_id     the schedule's id to update.
     * @param student_present map of Student ID - Present to add.
     * @throws SQLException if a database access error occurs.
     */
    public void update(int schedule_id, Map<Integer, Boolean> student_present) throws SQLException {
        try {
            connect();

            st = con.prepareStatement(delete);
            delete(schedule_id);

            st = con.prepareStatement(insert);
            insert(schedule_id, student_present);
        } finally {
            close();
        }
    }

    /**
     * Delete all Attendances for the given Schedule.
     *
     * @param schedule_id schedule's id to delete.
     * @throws SQLException if a database access error occurs.
     */
    private void delete(int schedule_id) throws SQLException {
        st.setInt(1, schedule_id);
        st.executeUpdate();
    }

    /**
     * Insert a map of Student ID - Present for the given Schedule into database.
     *
     * @param schedule_id      course's id to insert.
     * @param student_presents list of subject_id to insert.
     * @throws SQLException if a database access error occurs.
     */
    private void insert(int schedule_id, Map<Integer, Boolean> student_presents) throws SQLException {
        for (Map.Entry<Integer, Boolean> s_p : student_presents.entrySet()) {
            st.setInt(1, schedule_id);
            st.setInt(2, s_p.getKey());
            st.setBoolean(3, s_p.getValue());
            st.executeUpdate();
        }
    }
}
